clear all
set more off
set mem 10000000
set matsize 10000

****************************************************************** 
*** Build File to Process RGGVY District Progress Reports  *******
****************************************************************** 

** Set file paths
do "$path_code/paths.do"

********************************************************************************
********************************************************************************

** Import from csv and clean
{
insheet using "$rggvy/rggvy district-wise progress reports.csv", double comma clear

*Rename variables
la var v1 "Sl. No."
la var v2 "Name of the District (Total no. of District covered)"
la var v3 "DPR Code"
la var v4 "5 Year Plan"
la var v5 "Name of Implementing Agency"
la var v6 "Date of Sanction"
la var v7 "Project cost Sanctioned ( in Rs. Lakhs.)"
la var v8 "Date of Award of Contract"
la var v9 "Awarded cost/Revised cost ( in Rs. Lakhs.)"
la var v10 "Total Amount Released ( in Rs. Lakhs.)"
la var v11 "Total Amount Released ( in %)"
la var v12 "Electrification of Un-/De-Electrified villages, revised coverage"
la var v13 "Electrification of Un-/De-Electrified villages, achievement in number"
la var v14 "Electrification of Un-/De-Electrified villages, achievement in %"
la var v15 "Intensive Electrification of Electrified villages, revised coverage"
la var v16 "Intensive Electrification of Electrified villages, achievement in number"
la var v17 "Intensive Electrification of Electrified villages, achievement in %"
la var v18 "No. of Connections to BPL Households, revised coverage"
la var v19 "No. of Connections to BPL Households, achievement in number"
la var v20 "No. of Connections to BPL Households, achievement in %"

rename v1 si_no
rename v2 district
rename v3 dpr_code
rename v4 plan
rename v5 implement
rename v6 sanction_date
rename v7 sanction_cost
rename v8 award_date
rename v9 award_cost
rename v10 total_released
rename v11 pct_released
rename v12 coverage_UDE
rename v13 achiev_UDE
rename v14 achiev_pct_UDE
rename v15 coverage_ELEC
rename v16 achiev_ELEC
rename v17 achiev_pct_ELEC
rename v18 coverage_BPL
rename v19 achiev_BPL
rename v20 achiev_pct_BPL

compress

*Fix dates
foreach var of varlist *date {
 gen `var'1 = `var'
 replace `var'1 = "" if `var'1=="-"
 replace `var'1 = subinstr(`var'1,"/","-",2) 
 replace `var'1 = substr(`var'1,1,length(`var'1)-2) + "20" + substr(`var'1,-2,2) if substr(`var'1,-3,1)=="-"
 gen `var'2 = date(`var'1,"DMY")
 format `var'2 %d
 drop `var' `var'1
 rename `var'2 `var'
}

la var sanction_date "Date of Sanction"
la var award_date "Date of Award of Contract"
order si_no-implement sanction_date sanction_cost award_date

*Fix totals
replace implement = implement[_n-1] if implement=="" & regexm(substr(si_no,20,100),implement[_n-1])==1
replace plan = plan[_n-1] if plan=="" & regexm(substr(si_no,10,100),plan[_n-1])==1
replace si_no = "State-Implement-Plan Total" if dpr_code==. & plan!="" & implement!=""
replace si_no = "State-Plan Total" if dpr_code==. & plan!="" & implement==""
replace implement = "ALL" if implement=="" & si_no=="State-Plan Total"
replace si_no = "State Total" if regexm(si_no,"Total of All District")==1 & plan=="" & implement==""
replace si_no = "Others Total" if regexm(si_no,"Others")==1 & plan=="" & implement==""
replace implement = "ALL" if implement=="" & (si_no=="State Total" | si_no=="Others Total")
replace plan = "ALL" if plan=="" & (si_no=="State Total" | si_no=="Others Total")

*State codes
tostring dpr_code, replace
replace dpr_code = "0" + dpr_code if length(dpr_code)==3
gen st_code = substr(dpr_code,1,2)
destring st_code, replace
gen state = ""
replace state="ANDHRA PRADESH" 				if st_code==28
replace state="ARUNACHAL PRADESH" 			if st_code==12
replace state="ASSAM" 						if st_code==18
replace state="BIHAR" 						if st_code==10
replace state="CHHATTISGARH" 				if st_code==22
replace state="GUJARAT"						if st_code==24
replace state="HARYANA"						if st_code==6
replace state="HIMACHAL PRADESH"			if st_code==2
replace state="JAMMU & KASHMIR"				if st_code==1
replace state="JHARKHAND"					if st_code==20
replace state="KARNATAKA"					if st_code==29
replace state="KERALA"						if st_code==32
replace state="MADHYA PRADESH"				if st_code==23
replace state="MAHARASHTRA"					if st_code==27
replace state="MANIPUR"						if st_code==14
replace state="MEGHALAYA"					if st_code==17
replace state="MIZORAM"						if st_code==15
replace state="NAGALAND"					if st_code==13
replace state="ORISSA"						if st_code==21
replace state="PUNJAB"						if st_code==3
replace state="RAJASTHAN"					if st_code==8
replace state="SIKKIM"						if st_code==11
replace state="TAMIL NADU"					if st_code==33
replace state="TRIPURA"						if st_code==16
replace state="UTTAR PRADESH"		   		if st_code==9
replace state="UTTARAKHAND"					if st_code==5
replace state="WEST BENGAL"					if st_code==19
replace state="CHANDIGARH"					if st_code==4
replace state="DEHLI"						if st_code==7
replace state="DAMAN AND DIU"		   	 	if st_code==25
replace state="DADRA AND NAGAR HAVELI"		if st_code==26
replace state="GOA"							if st_code==30
replace state="LAKSHDWEEP"					if st_code==31
replace state="PONDICHERRY"					if st_code==34
replace state="ANDAMAN AND NICOBAR ISLANDS" if st_code==35
tostring st_code, replace

replace state = state[_n-1] if state=="" & state[_n-1]!="" & regexm(si_no,"Total")==1
replace st_code = st_code[_n-1] if st_code=="." & state==state[_n-1]

*Other cleaning
duplicates drop
drop if si_no==""

foreach var of varlist *pct*  {
 replace `var' = subinstr(`var',"%","",1) 
 destring `var', replace
}

destring st_code dpr_code, replace
order state st_code district dpr_code plan implement sanction_date-achiev_pct_BPL

save "$rggvy/rggvy_district_progress_X_XI_totals.dta", replace
keep if district!=""
save "$rggvy/rggvy_district_progress_X_XI.dta", replace
}


** Assign implementing agency names and types
{
use "$rggvy/rggvy_district_progress_X_XI.dta", clear

gen implement_type = ""
/* Central Public Sector Undertakings, State Depts of Power, State Electricity Boards, */
replace implement_type = "CPSU" if implement_type=="" & state=="ASSAM" & implement == "POWER GRID"
replace implement_type = "CPSU" if implement_type=="" & state=="BIHAR" & implement == "POWER GRID"
replace implement_type = "CPSU" if implement_type=="" & state=="CHHATTISGARH" & implement == "POWER GRID"
replace implement_type = "CPSU" if implement_type=="" & state=="GUJARAT" & implement == "POWER GRID"
replace implement_type = "CPSU" if implement_type=="" & state=="ORISSA" & implement == "POWER GRID"
replace implement_type = "CPSU" if implement_type=="" & state=="RAJASTHAN" & implement == "POWER GRID"
replace implement_type = "CPSU" if implement_type=="" & state=="TRIPURA" & implement == "POWER GRID"
replace implement_type = "CPSU" if implement_type=="" & state=="UTTAR PRADESH" & implement == "POWER GRID"
replace implement_type = "CPSU" if implement_type=="" & state=="WEST BENGAL" & implement == "POWER GRID"
replace implement_type = "CPSU" if implement_type=="" & state=="CHHATTISGARH" & implement == "NTPC"
replace implement_type = "CPSU" if implement_type=="" & state=="JHARKHAND" & implement == "NTPC"
replace implement_type = "CPSU" if implement_type=="" & state=="MADHYA PRADESH" & implement == "NTPC"
replace implement_type = "CPSU" if implement_type=="" & state=="ORISSA" & implement == "NTPC"
replace implement_type = "CPSU" if implement_type=="" & state=="WEST BENGAL" & implement == "NTPC"
replace implement_type = "CPSU" if implement_type=="" & state=="BIHAR" & implement == "NHPC"
replace implement_type = "CPSU" if implement_type=="" & state=="CHHATTISGARH" & implement == "NHPC"
replace implement_type = "CPSU" if implement_type=="" & state=="JAMMU & KASHMIR" & implement == "NHPC"
replace implement_type = "CPSU" if implement_type=="" & state=="ORISSA" & implement == "NHPC"
replace implement_type = "CPSU" if implement_type=="" & state=="WEST BENGAL" & implement == "NHPC"
replace implement_type = "CPSU" if implement_type=="" & state=="JHARKHAND" & implement == "DVC"
replace implement_type = "CPSU" if implement_type=="" & state=="WEST BENGAL" & implement == "DVC"
replace implement_type = "SDOP" if implement_type=="" & state=="ARUNACHAL PRADESH" & implement == "DOP AP"
replace implement_type = "SDOP" if implement_type=="" & state=="MIZORAM" & implement == "GOVT MIZO"
replace implement_type = "SDOP" if implement_type=="" & state=="NAGALAND" & implement == "DOP NAGALAND"
replace implement_type = "SDOP" if implement_type=="" & state=="SIKKIM" & implement == "DOP SIKKIM"
replace implement_type = "SDOP" if implement_type=="" & state=="MANIPUR" & implement == "GOVT MANI"
replace implement_type = "SEB"  if implement_type=="" & state=="HIMACHAL PRADESH" & implement == "HPSEB"
replace implement_type = "SEB"  if implement_type=="" & state=="JHARKHAND" & implement == "JSEB"
replace implement_type = "SEB"  if implement_type=="" & state=="KERALA" & implement == "KSEB"
replace implement_type = "SEB"  if implement_type=="" & state=="PUNJAB" & implement == "PSEB"
replace implement_type = "DISCOM" if implement_type=="" & state=="ANDHRA PRADESH" & implement=="EPDCO AP"
replace implement_type = "DISCOM" if implement_type=="" & state=="ANDHRA PRADESH" & implement=="SPDCO AP"
replace implement_type = "DISCOM" if implement_type=="" & state=="ANDHRA PRADESH" & implement=="CPDCO AP"
replace implement_type = "DISCOM" if implement_type=="" & state=="ANDHRA PRADESH" & implement=="NPDCO AP"
replace implement_type = "DISCOM" if implement_type=="" & state=="GUJARAT" & implement=="MGVCL"
replace implement_type = "DISCOM" if implement_type=="" & state=="GUJARAT" & implement=="DGVCL"
replace implement_type = "DISCOM" if implement_type=="" & state=="GUJARAT" & implement=="PGVCL"
replace implement_type = "DISCOM" if implement_type=="" & state=="GUJARAT" & implement=="UGVCL"
replace implement_type = "DISCOM" if implement_type=="" & state=="HARYANA" & implement=="UHBVNL"
replace implement_type = "DISCOM" if implement_type=="" & state=="HARYANA" & implement=="DHBVNL"
replace implement_type = "DISCOM" if implement_type=="" & state=="KARNATAKA" & implement=="BESCOM"
replace implement_type = "DISCOM" if implement_type=="" & state=="KARNATAKA" & implement=="CESCO"
replace implement_type = "DISCOM" if implement_type=="" & state=="KARNATAKA" & implement=="GESCOM"
replace implement_type = "DISCOM" if implement_type=="" & state=="KARNATAKA" & implement=="HESCO"
replace implement_type = "DISCOM" if implement_type=="" & state=="KARNATAKA" & implement=="MESCOM"
replace implement_type = "DISCOM" if implement_type=="" & state=="MADHYA PRADESH" & implement=="MPPuKVVCL"
replace implement_type = "DISCOM" if implement_type=="" & state=="MADHYA PRADESH" & implement=="MPPsKVVCL"
replace implement_type = "DISCOM" if implement_type=="" & state=="MADHYA PRADESH" & implement=="MPMKVVCL"
replace implement_type = "DISCOM" if implement_type=="" & state=="MAHARASHTRA" & implement=="MSEDCL"
replace implement_type = "DISCOM" if implement_type=="" & state=="RAJASTHAN" & implement=="AVVNL"
replace implement_type = "DISCOM" if implement_type=="" & state=="RAJASTHAN" & implement=="JVVNL"
replace implement_type = "DISCOM" if implement_type=="" & state=="RAJASTHAN" & implement=="JDVVNL"
replace implement_type = "DISCOM" if implement_type=="" & state=="UTTAR PRADESH" & implement=="PuVVNL"
replace implement_type = "DISCOM" if implement_type=="" & state=="UTTAR PRADESH" & implement=="MVVNL"
replace implement_type = "DISCOM" if implement_type=="" & state=="UTTAR PRADESH" & implement=="DVVNL"
replace implement_type = "DISCOM" if implement_type=="" & state=="UTTAR PRADESH" & implement=="PsVVNL"
replace implement_type = "DISCOM" if implement_type=="" & state=="UTTARAKHAND" & implement=="UPCL"
replace implement_type = "DISCOM" if implement_type=="" & state=="WEST BENGAL" & implement=="WBSEB"
replace implement_type = "DISCOM" if implement_type=="" & state=="CHHATTISGARH" & implement=="CSPDCL"
replace implement_type = "DISCOM" if implement_type=="" & state=="ASSAM" & implement=="ASSAM,SEB"
replace implement_type = "DISCOM" if implement_type=="" & state=="BIHAR" & implement=="BSEB"
replace implement_type = "DISCOM" if implement_type=="" & state=="MEGHALAYA" & implement=="MESEB"
replace implement_type = "DISCOM" if implement_type=="" & state=="TAMIL NADU" & implement=="TNEB"
replace implement_type = "DISCOM" if implement_type=="" & state=="JAMMU & KASHMIR" & implement=="PDD,JAMMU"
replace implement_type = "DISCOM" if implement_type=="" & state=="TRIPURA" & implement=="DOP TRIPURA"
replace implement_type = "COOP" if implement_type=="" & state=="ANDHRA PRADESH" & implement=="ARCOOP"
replace implement_type = "COOP" if implement_type=="" & state=="ANDHRA PRADESH" & implement=="CRCOOP"
replace implement_type = "COOP" if implement_type=="" & state=="ANDHRA PRADESH" & implement=="RESCO"
replace implement_type = "COOP" if implement_type=="" & state=="ANDHRA PRADESH" & implement=="CESS"
replace implement_type = "COOP" if implement_type=="" & state=="KARNATAKA" & implement=="HKR RE CO"

gen implement_full = ""
replace implement_full = "Power Grid Corporation"										if implement_full=="" & state=="ASSAM" & implement == "POWER GRID"
replace implement_full = "Power Grid Corporation"										if implement_full=="" & state=="BIHAR" & implement == "POWER GRID"
replace implement_full = "Power Grid Corporation"										if implement_full=="" & state=="CHHATTISGARH" & implement == "POWER GRID"
replace implement_full = "Power Grid Corporation"										if implement_full=="" & state=="GUJARAT" & implement == "POWER GRID"
replace implement_full = "Power Grid Corporation"										if implement_full=="" & state=="ORISSA" & implement == "POWER GRID"
replace implement_full = "Power Grid Corporation"										if implement_full=="" & state=="RAJASTHAN" & implement == "POWER GRID"
replace implement_full = "Power Grid Corporation"										if implement_full=="" & state=="TRIPURA" & implement == "POWER GRID"
replace implement_full = "Power Grid Corporation"										if implement_full=="" & state=="UTTAR PRADESH" & implement == "POWER GRID"
replace implement_full = "Power Grid Corporation"										if implement_full=="" & state=="WEST BENGAL" & implement == "POWER GRID"
replace implement_full = "National Thermal Power Corporation Ltd"		if implement_full=="" & state=="CHHATTISGARH" & implement == "NTPC"
replace implement_full = "National Thermal Power Corporation Ltd"		if implement_full=="" & state=="JHARKHAND" & implement == "NTPC"
replace implement_full = "National Thermal Power Corporation Ltd"		if implement_full=="" & state=="MADHYA PRADESH" & implement == "NTPC"
replace implement_full = "National Thermal Power Corporation Ltd"		if implement_full=="" & state=="ORISSA" & implement == "NTPC"
replace implement_full = "National Thermal Power Corporation Ltd"		if implement_full=="" & state=="WEST BENGAL" & implement == "NTPC"
replace implement_full = "National Hydroelectric Power Corporation"	if implement_full=="" & state=="BIHAR" & implement == "NHPC"
replace implement_full = "National Hydroelectric Power Corporation"	if implement_full=="" & state=="CHHATTISGARH" & implement == "NHPC"
replace implement_full = "National Hydroelectric Power Corporation"	if implement_full=="" & state=="JAMMU & KASHMIR" & implement == "NHPC"
replace implement_full = "National Hydroelectric Power Corporation"	if implement_full=="" & state=="ORISSA" & implement == "NHPC"
replace implement_full = "National Hydroelectric Power Corporation"	if implement_full=="" & state=="WEST BENGAL" & implement == "NHPC"
replace implement_full = "Damodar Valley Corporation"				 				if implement_full=="" & state=="JHARKHAND" & implement == "DVC"
replace implement_full = "Damodar Valley Corporation"				 				if implement_full=="" & state=="WEST BENGAL" & implement == "DVC"
replace implement_full = "Dept of Power, Arunachal Pradesh" 				if implement_full=="" & state=="ARUNACHAL PRADESH" & implement == "DOP AP"
replace implement_full = "Power & Electricity Department, Mizoram" 	if implement_full=="" & state=="MIZORAM" & implement == "GOVT MIZO"
replace implement_full = "Dept of Power, Nagaland"					 				if implement_full=="" & state=="NAGALAND" & implement == "DOP NAGALAND"
replace implement_full = "Dept of Power, Sikkim"						 				if implement_full=="" & state=="SIKKIM" & implement == "DOP SIKKIM"
replace implement_full = "Electrical Department, Manipur"		 				if implement_full=="" & state=="MANIPUR" & implement == "GOVT MANI"
replace implement_full = "Himachal Pradesh State Electricity Board" if implement_full=="" & state=="HIMACHAL PRADESH" & implement == "HPSEB"
replace implement_full = "Jharkhand State Electricity Board" 				if implement_full=="" & state=="JHARKHAND" & implement == "JSEB"
replace implement_full = "Kerala State Electricity Board" 	 				if implement_full=="" & state=="KERALA" & implement == "KSEB"
replace implement_full = "Punjab State Electricity Board"    				if implement_full=="" & state=="PUNJAB" & implement == "PSEB"
replace implement_full = "Eastern Power Distribution Company Ltd" if implement_full=="" & state=="ANDHRA PRADESH" & implement=="EPDCO AP"
replace implement_full = "Southern Power Distribution Company Ltd" if implement_full=="" & state=="ANDHRA PRADESH" & implement=="SPDCO AP"
replace implement_full = "Central Power Distribution Company Ltd" if implement_full=="" & state=="ANDHRA PRADESH" & implement=="CPDCO AP"
replace implement_full = "Northern Power Distribution Company Ltd" if implement_full=="" & state=="ANDHRA PRADESH" & implement=="NPDCO AP"
replace implement_full = "Madhya Gujrat Vij Company Ltd" if implement_full=="" & state=="GUJARAT" & implement=="MGVCL"
replace implement_full = "Dakshin Gujrat Vij Company Ltd" if implement_full=="" & state=="GUJARAT" & implement=="DGVCL"
replace implement_full = "Paschim Gujrat Vij Company Ltd" if implement_full=="" & state=="GUJARAT" & implement=="PGVCL"
replace implement_full = "Uttar Gujrat Vij Company Ltd" if implement_full=="" & state=="GUJARAT" & implement=="UGVCL"
replace implement_full = "Uttar Haryana Bijli Vitran Nigam Ltd" if implement_full=="" & state=="HARYANA" & implement=="UHBVNL"
replace implement_full = "Dakshin Haryana Bijli Vitran Nigam Ltd" if implement_full=="" & state=="HARYANA" & implement=="DHBVNL"
replace implement_full = "Bangalore Electricity Supply Company Ltd" if implement_full=="" & state=="KARNATAKA" & implement=="BESCOM"
replace implement_full = "Chamundeshwari Electricity Supply Company Ltd" if implement_full=="" & state=="KARNATAKA" & implement=="CESCO"
replace implement_full = "Gulbarga Electricity Supply Co. Ltd" if implement_full=="" & state=="KARNATAKA" & implement=="GESCOM"
replace implement_full = "Hubli Electricity Supply Co. Ltd" if implement_full=="" & state=="KARNATAKA" & implement=="HESCO"
replace implement_full = "Mangalore Electricity Supply Company Ltd" if implement_full=="" & state=="KARNATAKA" & implement=="MESCOM"
replace implement_full = "MP Poorv Kshetra Vidyut Vitran Company Ltd" if implement_full=="" & state=="MADHYA PRADESH" & implement=="MPPuKVVCL"
replace implement_full = "MP Paschim Kshetra Vidyut Vitran Company Ltd" if implement_full=="" & state=="MADHYA PRADESH" & implement=="MPPsKVVCL"
replace implement_full = "Madhya Kshetriya Vidyut Vitran Company Ltd" if implement_full=="" & state=="MADHYA PRADESH" & implement=="MPMKVVCL"
replace implement_full = "Maharashtra State Electricity Distribution Company Ltd" if implement_full=="" & state=="MAHARASHTRA" & implement=="MSEDCL"
replace implement_full = "Ajmer Vidyut Vitaran Nigam Ltd" if implement_full=="" & state=="RAJASTHAN" & implement=="AVVNL"
replace implement_full = "Jaipur Vidyut Vitaran Nigam Ltd" if implement_full=="" & state=="RAJASTHAN" & implement=="JVVNL"
replace implement_full = "Jodhpur Vidyut Vitaran Nigam Ltd" if implement_full=="" & state=="RAJASTHAN" & implement=="JDVVNL"
replace implement_full = "Purvanchal Vidyut Vitaran Nigam Ltd" if implement_full=="" & state=="UTTAR PRADESH" & implement=="PuVVNL"
replace implement_full = "Madhyanchal Vidyut Vitaran Nigam Ltd" if implement_full=="" & state=="UTTAR PRADESH" & implement=="MVVNL"
replace implement_full = "Dakishananchal Vidyut Vitaran Nigam Ltd" if implement_full=="" & state=="UTTAR PRADESH" & implement=="DVVNL"
replace implement_full = "Paschimanchal Vidyut Vitaran Nigam Ltd" if implement_full=="" & state=="UTTAR PRADESH" & implement=="PsVVNL"
replace implement_full = "Uttranchal Power Corporation Ltd" if implement_full=="" & state=="UTTARAKHAND" & implement=="UPCL"
replace implement_full = "West Bengal State Electricity Distribution Company Ltd" if implement_full=="" & state=="WEST BENGAL" & implement=="WBSEB"
replace implement_full = "Chhattisgarh State Power Distribution Company Ltd" if implement_full=="" & state=="CHHATTISGARH" & implement=="CSPDCL"
replace implement_full = "Assam Power Distribution Company Ltd" if implement_full=="" & state=="ASSAM" & implement=="ASSAM,SEB"
replace implement_full = "North/South Bihar Power Distribution Company Ltd" if implement_full=="" & state=="BIHAR" & implement=="BSEB"
replace implement_full = "Meghalaya Power Distribution Company Ltd" if implement_full=="" & state=="MEGHALAYA" & implement=="MESEB"
replace implement_full = "Tamil Nadu Generation and Distribution Corporation" if implement_full=="" & state=="TAMIL NADU" & implement=="TNEB"
replace implement_full = "J.K. Power Development Corporation Ltd" if implement_full=="" & state=="JAMMU & KASHMIR" & implement=="PDD,JAMMU"
replace implement_full = "Tripura State Electricity Corporation Ltd" if implement_full=="" & state=="TRIPURA" & implement=="DOP TRIPURA"
replace implement_full = "Anakapalle Rural Electric Cooperative Society" if implement_full=="" & state=="ANDHRA PRADESH" & implement=="ARCOOP"
replace implement_full = "Cheepurupalli Rural Electric Cooperative Society" if implement_full=="" & state=="ANDHRA PRADESH" & implement=="CRCOOP"
replace implement_full = "Kuppam Rural Electric Cooperative Society" if implement_full=="" & state=="ANDHRA PRADESH" & implement=="RESCO"
replace implement_full = "Sircilla Cooperative Electric Supply Society" if implement_full=="" & state=="ANDHRA PRADESH" & implement=="CESS"
replace implement_full = "Hukeri Taluka Rural Electric Cooperative Society" if implement_full=="" & state=="KARNATAKA" & implement=="HKR RE CO"
}


** Assign district codes
{
gen district_rggvy = district
replace district = "FATEHGARH SAHIB" if district=="FATEHGARHSAHIB" & state=="PUNJAB"
replace district = "GARHWAL" if district=="PAURI GARHWAL" & state=="UTTARAKHAND"
replace district = "SANT RAVIDAS NAGAR BHADOHI" if district=="SANT RAVIDAS NAGAR" & state=="UTTAR PRADESH"
replace district = "MAHRAJGANJ" if district=="MAHARAJGANJ" & state=="UTTAR PRADESH"
replace district = "PASHCHIM CHAMPARAN" if district=="PASCHIM CHAMPARAN" & state=="BIHAR"
replace district = "PURBA CHAMPARAN" if district=="PURAV CHAMPARAN" & state=="BIHAR"
replace district = "SENAPATI (EXCLUDING 3 SUB-DIVISIONS)" if district=="SENAPATI" & state=="MANIPUR"
replace district = "HAZARIBAGH" if district=="HAZARIBAG" & state=="JHARKHAND"
replace district = "BASTAR" if district=="BASTER" & state=="CHHATTISGARH"
replace district = "CHAMARAJANAGAR" if district=="CHAMRAJNAGAR" & state=="KARNATAKA"
replace district = "DAVANAGERE" if district=="DAVANGERE" & state=="KARNATAKA"
replace district = "KURUNG KUMEY" if district=="KURUNG KUMAI" & state=="ARUNACHAL PRADESH"
replace district = "SARAIKELA-KHARSAWAN" if district=="SARAIKELA KHARSAWAN" & state=="JHARKHAND"
replace district = "ASHOKNAGAR" if district=="ASHOK NAGAR" & state=="MADHYA PRADESH"
replace district = "ARIYALUR" if district=="ARIYALUR KRISHNAGIRI" & state=="TAMIL NADU"

merge m:m st_code district using "$pca/pca_census01_names.dta", keepusing(dt_code) keep(1 3) nogen
duplicates drop
rename district district11
merge m:m st_code district11 using "$pca/pca_census11_names.dta", keepusing(dt_code11 dt_code) keep(1 3 4 5) nogen update
duplicates drop

rename district11 district
replace dt_code = 90 if dt_code==.
order st_code dt_code state district_rggvy district
}

** Add plan numbers and virtual dprs
{
replace plan = "10" if plan=="10th Plan"
replace plan = "11" if plan=="11th Plan"
destring plan, replace

egen stdt = group(st_code dt_code)
sum stdt
egen vplan4 = mean(plan), by(stdt)
egen vdpr4 = mean(dpr_code), by(stdt)

order state st_code district dt_code dpr_code plan vdpr4 vplan4
}

** Save full summary table
{
compress 
save "$rggvy/rggvy_district_progress_X_XI_processed.dta", replace
}

** Make version for maps
{
*Get a unique stdt dataset
use "$rggvy/rggvy_district_progress_X_XI_processed.dta", clear
keep state st_code district dt_code vplan4
duplicates drop st_code dt_code, force

*Flag districts that aren't RGGVY districts
rename state state_name
rename district district_name
rename st_code state
rename dt_code district
merge 1:m state district using "$pca/pca_census01_india.dta", keepusing(state district)
duplicates drop
drop if _merge==1
gen non_rggvy = _merge==2
drop _merge
rename state st_code
rename district dt_code
rename state_name state
rename district_name district

*Flag remaining districts that aren't in the lights data
merge 1:m st_code dt_code using "$nl/nightlights_outcomes_avg_vis.dta", keepusing(st_code dt_code)
duplicates drop
drop if dt_code==0
replace non_rggvy = 1 if _merge==2
gen noGIS_dt = _merge==1
egen noGIS = min(noGIS_dt), by(st_code)
gen rggvy_no_GIS = (noGIS==1 & non_rggvy==0)
drop noGIS noGIS_dt _merge

*Remaining flags
gen rggvy_11_only = (non_rggvy==0 & rggvy_no_GIS==0 & vplan4==11)
gen sample_state = inlist(st_code,1,2,8,10,19,21,22,23,24,27,28,29)
gen rggvy_10_not_sample_state = (non_rggvy==0 & rggvy_no_GIS==0 & rggvy_11_only==0 & sample_state==0)
gen rggvy_10_sample_state = (non_rggvy==0 & rggvy_no_GIS==0 & rggvy_11_only==0 & sample_state==1)

*Save
assert non_rggvy + rggvy_no_GIS + rggvy_11_only + rggvy_10_not_sample_state + rggvy_10_sample_state==1
compress
rename vplan4 plan
keep st_code dt_code  plan non_rggvy rggvy_no_GIS rggvy_11_only rggvy_10_not_sample_state rggvy_10_sample_state
order st_code dt_code plan non_rggvy rggvy_no_GIS rggvy_11_only rggvy_10_not_sample_state rggvy_10_sample_state
save "$rggvy/rggvy_districts_for_map.dta", replace
}

********************************************************************************
********************************************************************************
